/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Stefan
 */
public class DBCustomer implements IFDBCustomer
{

    private  Connection con;
    /** Creates a new instance of DBCustomer */
    public DBCustomer() 
    {
      con = DBConnection.getInstance().getDBcon();
    }

    //method to delete a customer
    public int deleteCustomer(int id)
    {
        int rc=-1;

        String query="DELETE FROM customer WHERE customerID = '" +
                        id + "'";
        System.out.println(query);
        try{ // delete from customer
                con.setAutoCommit(false);
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                rc = stmt.executeUpdate(query);
                stmt.close();
                con.commit();
                con.setAutoCommit(true);
        }//slut try
        catch(Exception ex){
                System.out.println("Delete exception in customer db: "+ex);

                try{
                    rc=-1;
                    con.rollback();
                    con.setAutoCommit(true);
                }
                catch(Exception ex1)
                {
                    System.out.println("Error rolling back" + ex1);
                }
        }
        return(rc);
    }

    //method to find a customer with id
    public Customer findCustomer(int id, boolean retriveAssociation)
    {
        String wClause = "  customerID = '" + id + "'";
        return singleWhere(wClause, retriveAssociation);
    }

    //methode til at finde alle kunder
    public ArrayList<Customer> getAllCustomer(boolean retriveAssociation) {
        return miscWhere("", retriveAssociation);
    }

    //method to insert a new customer
    public int insertCustomer(Customer cus)
    {
        //call to get the next customer ID
        int nextID = GetMax.getMaxId("Select max(customerID) from customer");
        nextID = nextID + 1;
        System.out.println("next customerID = " +  nextID);

        int rc = -1;
	String query="INSERT INTO customer(customerID, name, address, pCode, cCode, phoneNumber, type)  VALUES('"+
	  	nextID + "','" +
                cus.getName()  + "','"  +
		cus.getAddress()  + "','"  +
                cus.getZipCode() + "','" +
                cus.getCountryCode() + "','" +
                cus.getPhoneNo() + "','" +
                cus.getType() + "')";
       System.out.println("insert : " + query);
       try{ // insert new customer
          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
          con.commit();
          con.setAutoCommit(true);
       }//end try
       catch(SQLException ex){
          System.out.println("Customer ikke oprettet");
          System.out.println("Insert exception in customer db: "+ex);
	  try{
             rc=-1;
             con.rollback();
             con.setAutoCommit(true);
          }
          catch(Exception ex1)
          {
             System.out.println("Error rolling back"+ ex1);
          }
       }
        return(rc);
    }

    //method to find a customer by name
    public Customer searchCustomerName(String attValue, boolean retriveAssociation) {
        String wClause = "name like '%" + attValue + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }

    //method for updating a customer
    public int updateCustomer(Customer cus) {
            Customer cusObj  = cus;
            int rc=-1;

            String query="UPDATE customer SET "+
                      "name ='"+ cusObj.getName() +"', "+
                      "address ='"+ cusObj.getAddress() + "', " +
                      "pCode ='"+ cusObj.getZipCode() + "', " +
                      "cCode ='"+ cusObj.getCountryCode() + "', " +
                      "phoneNumber ='"+ cusObj.getPhoneNo() + "' " +
                      " WHERE customerID = '"+ cusObj.getCustomerID() + "'";
            System.out.println("Update query:" + query);
            try{ // update customer
                    Statement stmt = con.createStatement();
                    stmt.setQueryTimeout(5);
                    rc = stmt.executeUpdate(query);

                    stmt.close();
            }//slut try
            catch(Exception ex){
                    System.out.println("Update exception in customer db: "+ex);
            }
            return(rc);
    }
    
    //private methods
    //michWere is used whenever we want to select more than one customer
    private ArrayList<Customer> miscWhere(String wClause, boolean retriveAssociation)
    {
        ResultSet results;
	ArrayList<Customer> list = new ArrayList<Customer>();	
        String query =  buildQuery(wClause);
        try{ // read the customer from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            while(results.next() )
            {
	     	 Customer cusObj = new Customer();
		 cusObj = buildCustomer(results);	
                 list.add(cusObj);	
            }//end while
            stmt.close();
             if(retriveAssociation)
             {   //The city and country is to be build
                 for(Customer cusObj : list){
                    String pCode = cusObj.getZipCode();
                    DBCity getCity = new DBCity();
                    String city = getCity.getCityName(pCode);
                    cusObj.setCity(city);
                    System.out.println("City is seleceted");
                    String countryCode = cusObj.getCountryCode();
                    DBCountry dbCount = new DBCountry();
                    String countryName = dbCount.getCountryName(countryCode);
                    cusObj.setCountry(countryName);
                    System.out.println("Country is seleceted");
                 }
                }//end if
        }//slut try	
        catch(Exception e)
        {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
            return list;
    }
	
    //Singelwhere is used when we only select one customer
    private Customer singleWhere(String wClause, boolean retriveAssociation)
    {
        ResultSet results;
        Customer cusObj = new Customer();

        String query =  buildQuery(wClause);
        System.out.println(query);
        try{ // read the customer from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if( results.next() )
            {
                cusObj = buildCustomer(results);
                //assocaition is to be build
                stmt.close();
                if(retriveAssociation)
                {   //The city and country is to be build
                    String pCode = cusObj.getZipCode();
                    DBCity getCity = new DBCity();
                    String city = getCity.getCityName(pCode);
                    cusObj.setCity(city);
                    System.out.println("City is seleceted");
                    String countryCode = cusObj.getCountryCode();
                    DBCountry getCountry = new DBCountry();
                    String countryName = getCountry.getCountryName(countryCode);
                    cusObj.setCountry(countryName);
                    System.out.println("Country is seleceted");
                }
            }
            else{ //no customer was found
                cusObj = null;
            }
        }//end try	
        catch(Exception e){
            System.out.println("Query exception: "+e);
        }
        return cusObj;
    }
    //method to build the query
    private String buildQuery(String wClause)
    {
        String query="SELECT customerID, name, address, pCode, cCode, phoneNumber, type FROM customer";

            if (wClause.length()>0)
                    query=query+" WHERE "+ wClause;

            return query;
    }
    //method to build an customer object
    private Customer buildCustomer(ResultSet results)
    {   
        Customer cusObj = new Customer();
        try{ // the columns from the table emplayee  are used
            cusObj.setCustomerID(results.getInt("customerID"));
            cusObj.setName(results.getString("name"));
            cusObj.setAddress(results.getString("address"));
            cusObj.setZipCode(results.getString("pCode"));
            cusObj.setCountryCode(results.getString("cCode"));
            cusObj.setPhoneNo(results.getString("phoneNumber"));
            cusObj.setType(results.getInt("type"));
        }
         catch(Exception e)
         {
         System.out.println("error in building the customer object");
         }
    return cusObj;
    }


}
